<?php
/**
 * Classe Model do usuario, conectando com o banco de dados.s
 * @author Rodrigo
 * @package App.Model
 */

/**
 * Usar componentes do CAKEPHP para a Autoriza��o de paginas.
 */
App::uses('AuthComponent', 'Controller/Component');

Class Community extends AppModel {

	public $useTable = 'communities';
	
	public $hasMany = array(
		        'Topic' => array(
		            'className' => 'Topic',
		            'order' => 'Topic.creation_date DESC'
		        )
		    );
	
	public $hasAndBelongsToMany = array( 'Tag' => array( 'className' => 'Tag', 
										'joinTable' => 'communities_tags', 'foreignKey' => 'community_id', 
										'associationForeignKey' => 'tag_id') );


	
	public function getIdByUrl($url){
		$result = $this->query("SELECT Community.id FROM communities AS Community WHERE url = '".$url."'");
		return $result[0]['Community']['id'];
	}

	public function getUrlById($id){
		$result = $this->query("SELECT Community.url FROM communities AS Community WHERE id = '".$id."'");
		return $result[0]['Community']['url'];
	}

	public function getInfosByUrl($url){
		$result = $this->query("SELECT * FROM communities AS Community WHERE url = '".$url."'");
		return $result[0];
	}
	
	//comunidades do usuario
	public function userCommunities($idUser) {
		$userCmm = $this->query("SELECT result.community_id, result.community_name, result.community_url, result.community_photo, COUNT(users_communities.user_id) AS members_amount FROM 
		(SELECT users_communities.community_id, communities.name AS community_name, communities.url AS community_url, communities.photo AS community_photo FROM users_communities
		LEFT JOIN communities ON users_communities.community_id = communities.id WHERE users_communities.user_id = ".$idUser." AND
		(users_communities.status = 'PENDENTE' OR users_communities.status = 'APROVADO' OR users_communities.status = 'MODERADOR'))result
		LEFT JOIN users_communities ON result.community_id = users_communities.community_id
		GROUP BY users_communities.community_id;");

		return $userCmm;
	}

	//comunidades que o usuario nao participa
	public function communities($idUser) {
		$cmm = $this->query("SELECT communities.id AS community_id, communities.name AS community_name,communities.photo AS community_photo, communities.url AS community_url, COUNT(result.user_id) AS members_amount FROM communities
		LEFT JOIN (SELECT users_communities.community_id AS community_id, users_communities.user_id AS user_id FROM users_communities
		WHERE users_communities.status = 'PENDENTE' OR users_communities.status = 'APROVADO' OR users_communities.status = 'MODERADOR')result ON communities.id = result.community_id
		WHERE communities.id NOT IN (SELECT users_communities.community_id FROM users_communities WHERE users_communities.user_id = ".$idUser.")
		GROUP BY communities.id;");
		return $cmm;
	}

	//quantos membros a comunidade tem
	public function membersCmm($idCmm){
		$members = $this ->query("select count(community_id) as members_amount from users_communities where users_communities.status <> 'BANIDO' and users_communities.status <> 'PENDENTE' and community_id = ".$idCmm);
		return $members[0][0]['members_amount'];
	}
	
	//membros moderadores da comunidade
	public function usersModerador($idCmm){
		$users = $this ->query("select users.first_name, users.last_name, users.username, users.photo, users.id from users, users_communities where users_communities.status = 'MODERADOR' and users_communities.user_id = users.id and users_communities.community_id = ".$idCmm);
		return $users;
	}
	
	//membros da comunidade
	public function users($idCmm){
		$users = $this ->query("select users.first_name, users.last_name, users.username, users.photo, users.id  from users, users_communities where users_communities.status = 'APROVADO' and users_communities.user_id = users.id and users_communities.community_id =  ".$idCmm);
		return $users;
	}

	//membros pendentes da comunidade
	public function usersPendentes($idCmm){
		$users = $this ->query("select users.first_name, users.last_name, users.username, users.photo, users.id  from users, users_communities where users_communities.status = 'PENDENTE' and users_communities.user_id = users.id and users_communities.community_id =  ".$idCmm);
		return $users;
	}


	
	//coloca o usuario como membro da comunidade
	public function joinAprovado($idUser, $idCmm){
		$this->query("INSERT INTO users_communities (user_id, community_id, status, date_admission) VALUES ('" . $idUser . "', '" . $idCmm. "', 'APROVADO', now())");
	}
	public function joinPendente($idUser, $idCmm){
		$this->query("INSERT INTO users_communities (user_id, community_id, status, date_admission) VALUES ('" . $idUser . "', '" . $idCmm. "', 'PENDENTE', now())");
	}

	public function joinModerador($idUser, $idCmm){
		$this->query("INSERT INTO users_communities (user_id, community_id, status, date_admission) VALUES ('" . $idUser . "', '" . $idCmm. "', 'MODERADOR', now())");
	}

	public function aprovar($idUser, $idCmm){
		$this->query("Update users_communities set status = 'APROVADO' where user_id = ".$idUser." and community_id = ".$idCmm);
	}

	public function banir($idUser, $idCmm){
		$this->query("Update users_communities set status = 'BANIDO' where user_id = ".$idUser." and community_id = ".$idCmm);
	}

	public function moderador($idUser, $idCmm){
		$this->query("Update users_communities set status = 'MODERADOR' where user_id = ".$idUser." and community_id = ".$idCmm);
	}
	
	public function deletar($idUser, $idCmm){
		$this->query("Delete from users_communities where user_id = ".$idUser." and community_id = ".$idCmm);
	}	

	//sai da comunidade
	public function leave($idUser, $idCmm){
		$this->query("delete from users_communities where user_id = '".$idUser."' and community_id = '".$idCmm."'");
	}
	
	//ver se o usuario participa
	public function userParticipa ($idUser, $idCmm) {
		$count = $this->query("SELECT status FROM users_communities WHERE user_id = ".$idUser." and community_id = ".$idCmm);
        
		if ($count){
			if ($count[0]['users_communities']['status'] == 'PENDENTE'){
                return 'p';
            
            }
			else if ($count[0]['users_communities']['status'] == 'BANIDO'){
                return 'b';
                
            }
			else if ($count[0]['users_communities']['status'] == 'APROVADO'){
                return 'a';
            }
            else if ($count[0]['users_communities']['status'] == 'MODERADOR'){
                return 'm';
            }
		}else{
			return 'n';
		}
	}

	//ver se o usuario participa e � moderador
	public function userModerador ($idUser, $idCmm) {
		$count = $this->query("SELECT count(*) as count FROM users_communities WHERE status = 'MODERADOR' and user_id = ".$idUser." and community_id = ".$idCmm);
		if ($count[0][0]['count'] > 0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * Buscar comunidades tendo como entrada uma parte da palavra/frase do titulo da comunidade
	 * @param  String $query Palavra a ser pesquisada
	 * @return array        Dicionario com as comunidades encontradas
	 */
	public function searchCommunities($query){
		$result = $this->query('SELECT * FROM communities WHERE name LIKE "%'.$query.'%"');
		return $result;
	}
    
    /**
     * Buscar posts das comunidades que o usuario faz parte
     * @param type $id Identificador do usuario
     * @return array Dicionario com os topicos encontrados
     */
    public function getTopicsFeed($id){
        $sql =  "SELECT cmm.community_id, cmm.community_name, cmm.community_url, topics.id AS topic_id, topics.name AS topic_name, topics.user_id AS topic_user_id, topics.url AS topic_url,posts.description AS topic_description FROM
                (SELECT users_communities.community_id, communities.name AS community_name, communities.url AS community_url FROM users_communities
                LEFT JOIN communities ON users_communities.community_id = communities.id WHERE users_communities.user_id = $id AND
                (users_communities.status = 'APROVADO' OR users_communities.status = 'MODERADOR'))cmm
                INNER JOIN topics ON cmm.community_id = topics.community_id
                INNER JOIN posts ON posts.id = (SELECT id FROM posts WHERE topic_id = topics.id LIMIT 1)

                LIMIT 0, 10;";
        $result = $this->query($sql);
        return $result;
    }
	
}